﻿using System;
using System.Collections.Generic;
using System.Text;
using Exam.Core.DBUtility;
using System.Data;
using System.IO;
using System.Xml;
using System.Data.OleDb;
using System.Collections;
using System.Data.SqlClient;

namespace Exam.Core.SQLDAL
{
    public class Paper {
        public int getSize(string DepartmentID, string starTime, string endTime, string keyword) {
            StringBuilder sb = new StringBuilder();
            sb.Append(" WHERE 1=1 ");
            if (DepartmentID.Trim() != "")
                sb.Append(" AND DepartmentID in(" + DepartmentID + ")");
            if (starTime.Trim() != "")
                sb.Append(" AND CreatTime >= '" + starTime + "'");
            if (endTime.Trim() != "")
                sb.Append(" AND CreatTime <= '" + endTime + "'");
            if (keyword.Trim() != "")
                sb.Append(" AND (PaperName like '%" + keyword.Trim() + "%' OR PaperDesc like '%" + keyword.Trim() + "%')");


            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT Count(*)  FROM Paper " + sb.ToString());

            object obj = DbHelperSQL.GetSingle(SQL.ToString());
            return int.Parse(obj.ToString());
        }

        public List<Exam.Core.Model.Paper> getPaperListByPage(string DepartmentID, int pagesize, int page, string starTime, string endTime, string keyword) {
            List<Exam.Core.Model.Paper> list = new List<Exam.Core.Model.Paper>();
            StringBuilder sb = new StringBuilder();
            sb.Append(" WHERE 1=1 ");
            if (DepartmentID.Trim() != "")
                sb.Append(" AND DepartmentID in(" + DepartmentID + ")");
            if (starTime.Trim() != "")
                sb.Append(" AND CreatTime >= '" + starTime + "'");
            if (endTime.Trim() != "")
                sb.Append(" AND CreatTime <= '" + endTime + "'");
            if (keyword.Trim() != "")
                sb.Append(" and (PaperName like '%" + keyword.Trim() + "%' OR PaperDesc like '%" + keyword.Trim() + "%')");
            
            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT TOP " + pagesize + "  *  FROM Paper ");
            if (page <= 0) {
                SQL.Append(sb.ToString());
            }
            else {
                SQL.Append(sb.ToString());
                SQL.Append(" AND PaperID NOT IN ( ");
                SQL.Append(" SELECT TOP " + pagesize * page + " PaperID FROM Paper ");
                SQL.Append(sb.ToString() + " ) ");
                
            }
            sb.Append(" order by PaperID desc");
            DataTable dt = DbHelperSQL.Query(SQL.ToString()).Tables[0];
            foreach (DataRow dr in dt.Rows) {
                Exam.Core.Model.Paper obj = new Exam.Core.Model.Paper();
                obj.PaperID = dr["PaperID"].ToString().Trim();
                obj.PaperName = dr["PaperName"].ToString().Trim();
                obj.PaperDesc = dr["PaperDesc"].ToString().Trim();
                obj.PaperTime = dr["PaperTime"].ToString().Trim();
                obj.CreatTime = dr["CreatTime"].ToString().Trim();
                obj.StarTime = dr["StarTime"].ToString().Trim();
                obj.EndTime = dr["EndTime"].ToString().Trim();
                obj.PaperContent = dr["PaperContent"].ToString().Trim();
                obj.PaperType = dr["PaperType"].ToString().Trim();
                obj.CourseId = dr["CourseId"].ToString().Trim();
                obj.DepartmentID = dr["DepartmentID"].ToString().Trim();
                obj.PaperQuestionTypes = getQuestionType(obj.PaperContent.Trim());
                list.Add(obj);
            }
            return list;
        }

        private List<Exam.Core.Model.PaperQuestionType> getQuestionType(string _path) {

            string path = FilePath.PaperPath.Trim()+_path;
            if (!File.Exists(path))
                return null;

            List<Exam.Core.Model.PaperQuestionType> list = new List<Exam.Core.Model.PaperQuestionType>();

            XmlDocument doc = XmlHelper.GetXmlDocument(path);

            XmlNodeList nodeList = doc.SelectNodes("root/content/questiontype");

            List<Exam.Core.Model.QuestionType> TypeList = new QuestionType().getQuestionTypeList();

            foreach (XmlNode node in nodeList) {
                foreach (Exam.Core.Model.QuestionType qt in TypeList){
                    if (node.Attributes["id"].Value.Trim() == qt.QuestionTypeId.Trim()) {
                        Exam.Core.Model.PaperQuestionType obj = new Exam.Core.Model.PaperQuestionType();
                        obj.QuestionTypeId = qt.QuestionTypeId.Trim();
                        obj.QuestionTypeTitle = qt.QuestionTypeTitle.Trim();
                        obj.QuestionTypeAttr = qt.QuestionTypeAttr.Trim();
                        obj.Score = node.Attributes["score"].Value.Trim();
                        obj.QuestionIdList = node.SelectSingleNode("questionid").InnerText.Trim();
                        obj.QuestionCount = getCount(obj.QuestionIdList);
                        list.Add(obj);
                    }
                }
            }
            return list;

        }

        private string getCount(string p) {
            int count =p.Split(',').Length;
            return count.ToString();
        }

        public string QuestionIDList(List<string> SQLStringList){
            StringBuilder SB = new StringBuilder();
            DataTable dt = ExecSqlTran(SQLStringList);
            foreach (DataRow dr in dt.Rows) {
                SB.Append(dr["QuestionId"].ToString().Trim() + ",");
            }
            return SB.ToString().Trim(',');

        }

        public DataTable ExecSqlTran(List<string> SQLStringList) {
            DataSet ds = DbHelperSQL.GetDataSetBySQLList(SQLStringList);
            DataTable dt = new DataTable();//ds.Tables[0].Clone();
            dt.Columns.Add("QuestionId");
            object[] obj = new object[dt.Columns.Count];
            foreach (DataTable table in ds.Tables) {
                for (int i = 0; i < table.Rows.Count; i++) {
                    table.Rows[i].ItemArray.CopyTo(obj, 0);
                    dt.Rows.Add(obj);
                }
            }
            return dt;
        }

        public int Add(string pname, string ptime, string ptype, string pdesc,string creattime, string startime, string endtime, string paperFileName, string course,string deptid,string allowre) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Paper(");
            strSql.Append("PaperName,PaperDesc,PaperTime,CreatTime,StarTime,EndTime,PaperContent,PaperType,CourseId,DepartmentID,AllowRe)");
            strSql.Append(" values (");
            strSql.Append("@PaperName,@PaperDesc,@PaperTime,@CreatTime,@StarTime,@EndTime,@PaperContent,@PaperType,@CourseId,@DepartmentID,@AllowRe)");
            SqlParameter[] parameters = {
					new SqlParameter("@PaperName", SqlDbType.VarChar,250),
					new SqlParameter("@PaperDesc", SqlDbType.VarChar,0),
					new SqlParameter("@PaperTime", SqlDbType.VarChar,10),
					new SqlParameter("@CreatTime", SqlDbType.Date),
					new SqlParameter("@StarTime", SqlDbType.Date),
					new SqlParameter("@EndTime", SqlDbType.Date),
					new SqlParameter("@PaperContent", SqlDbType.VarChar,250),
					new SqlParameter("@PaperType", SqlDbType.Int,4),
					new SqlParameter("@CourseId", SqlDbType.Int,4),
                    new SqlParameter("@DepartmentID", SqlDbType.Int,4),
                    new SqlParameter("@AllowRe", SqlDbType.Int,4)
                                          };
            parameters[0].Value = pname;
            parameters[1].Value = pdesc;
            parameters[2].Value = ptime;
            parameters[3].Value = creattime;
            parameters[4].Value = startime;
            parameters[5].Value = endtime;
            parameters[6].Value = paperFileName;
            parameters[7].Value = int.Parse(ptype);
            parameters[8].Value = int.Parse(course);
            parameters[9].Value = int.Parse(deptid);
            parameters[10].Value = int.Parse(allowre);

            
            int result = 0;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if(rows>0){
                result = DbHelperSQL.GetMaxID("PaperID", "Paper");
                if (result > 0) {
                    return result;
                }
            }
            return 0;

        }
    
        public int AddStudentPaper(ArrayList sql){
            int rows = 0;
            try{
                DbHelperSQL.ExecuteSqlTran(sql);
                rows = 1;
            }
            catch (System.Exception ex){
                rows = 0;
            }
            return rows;
        }


        public Exam.Core.Model.ExamPaper getPaperById(int id) {
            Exam.Core.Model.ExamPaper model = new Exam.Core.Model.ExamPaper();
            string SQL = "SELECT * FROM Paper WHERE PaperID=" + id;
            using (SqlDataReader sdr = DbHelperSQL.ExecuteReader(SQL)) {
                if (sdr.HasRows) {
                    while (sdr.Read()) {
                        model.PaperID = sdr.GetValue(0).ToString().Trim();
                        model.PaperName = sdr.GetString(1).Trim();
                        model.PaperDesc = sdr.GetString(2).Trim();
                        model.PaperTime = sdr.GetString(3).Trim();
                        model.CreatTime = sdr.GetValue(4).ToString().Trim();
                        model.StarTime = sdr.GetValue(5).ToString().Trim();
                        model.EndTime = sdr.GetValue(6).ToString().Trim();
                        model.PaperContent = sdr.GetString(7).Trim();
                        model.PaperType = sdr.GetValue(8).ToString().Trim();
                        model.CourseId = sdr.GetValue(9).ToString().Trim();
                        model.DepartmentID = sdr.GetValue(10).ToString().Trim();
                        model.AllowRe = sdr.GetValue(11).ToString().Trim();
                    }
                }
            }
            model.PaperQuestionTypes = getQuestionType(model.PaperContent.Trim());
            model.ExamQuestionTypeList = getExamQType(model.PaperContent.Trim());//试卷下题型列表
            return model;

        }

        public List<Exam.Core.Model.ExamQuestionType> getExamQType(string _path) {
            string path = FilePath.PaperPath.Trim() + _path;
            if (!File.Exists(path))
                return null;

            List<Exam.Core.Model.ExamQuestionType> list = new List<Exam.Core.Model.ExamQuestionType>();

            XmlDocument doc = XmlHelper.GetXmlDocument(path);

            XmlNodeList nodeList = doc.SelectNodes("root/content/questiontype");

            List<Exam.Core.Model.QuestionType> TypeList = new QuestionType().getQuestionTypeList();

            foreach (XmlNode node in nodeList) {
                foreach (Exam.Core.Model.QuestionType qt in TypeList) {
                    if (node.Attributes["id"].Value.Trim() == qt.QuestionTypeId.Trim()) {
                        Exam.Core.Model.ExamQuestionType obj = new Exam.Core.Model.ExamQuestionType();
                        obj.QuestionTypeId = qt.QuestionTypeId.Trim();
                        obj.QuestionTypeTitle = qt.QuestionTypeTitle.Trim();
                        obj.QuestionTypeAttr = qt.QuestionTypeAttr.Trim();
                        obj.Score = node.Attributes["score"].Value.Trim();
                        //obj.QuestionIdList = node.SelectSingleNode("questionid").InnerText.Trim();
                        //obj.QuestionCount = getCount(obj.QuestionIdList);
                        string idlist = node.SelectSingleNode("questionid").InnerText.Trim();
                        obj.QuestionList = getQuestionList(idlist,obj.QuestionTypeId, obj.QuestionTypeAttr.Trim());//获取试题列表

                        list.Add(obj);
                    }
                }
            }
            return list;
        }

        private List<Exam.Core.Model.ExamQuestion> getQuestionList(string idlist,string qtypeid,string qtypeattr) {
            List<Exam.Core.Model.ExamQuestion> list = new List<Exam.Core.Model.ExamQuestion>();
            string SQL = "SELECT * FROM Question WHERE QuestionID in (" + idlist.Trim(',') + ")";
            using(SqlDataReader odr = DbHelperSQL.ExecuteReader(SQL)){
                if (odr.HasRows) {
                    while (odr.Read()) {
                        Exam.Core.Model.ExamQuestion obj = new Exam.Core.Model.ExamQuestion();
                        obj.QuestionId = odr.GetInt32(0).ToString().Trim();
                        obj.QuestionContent = odr.GetString(1).Trim();
                        obj.QuestionAnswer = odr.GetString(2).Trim();
                        obj.QuestionType = odr.GetInt32(3).ToString().Trim();
                        if (qtypeattr.Trim() == "0") {
                            obj.ExamOptsList = getOpts(obj.QuestionId.Trim());
                        }
                        list.Add(obj);
                    }
                }

            }
            return list;
        }

        private List<Exam.Core.Model.ExamOpts> getOpts(string qid) {
            string SQL = "SELECT * FROM QuestionOpts WHERE QuestionId=" + qid;
            DataTable dt = DbHelperSQL.Query(SQL).Tables[0];
            List<Exam.Core.Model.ExamOpts> list = new List<Exam.Core.Model.ExamOpts>();
            foreach(DataRow dr in dt.Rows){
                Exam.Core.Model.ExamOpts obj = new Exam.Core.Model.ExamOpts();
                obj.QuestionOptsID = dr["QuestionOptsID"].ToString().Trim();
                obj.QuestionOptsText = dr["QuestionOptsText"].ToString().Trim();
                obj.QuestionOptsValue = dr["QuestionOptsValue"].ToString().Trim();
                obj.QuestionId = qid.Trim();
                list.Add(obj);
            }
            return list;
        }

        public bool ExecuteSql(string SQL) {
            bool result = false;
            try {
                DbHelperSQL.ExecuteSql(SQL);
                result = true;
            }
            catch {
                result = false;
            }
            return result;
        }

    }
}
